/*****************************************************************************
 *   Copyright 2005 Tim A Wang
 *
 *   Licensed under the Apache License, Version 2.0 (the "License");
 *   you may not use this file except in compliance with the License.
 *   You may obtain a copy of the License at
 *
 *       http://www.apache.org/licenses/LICENSE-2.0
 *
 *   Unless required by applicable law or agreed to in writing, software
 *   distributed under the License is distributed on an "AS IS" BASIS,
 *   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *   See the License for the specific language governing permissions and
 *   limitations under the License.
 *
 ****************************************************************************/
package com.jb.web.bean.database;

import com.jb.database.metamodel.Column;
import com.jb.database.metamodel.Database;
import com.jb.database.metamodel.Schema;
import com.jb.database.metamodel.Table;
import com.jb.faces.el.AttributeEvaluator;
import com.jb.framework.DataSourceManager;
import com.jb.framework.PersistentContext;
import com.jb.framework.PersistentManagerRegistry;
import com.jb.framework.database.Predicate;
import com.jb.framework.database.SelectDBCommand;
import com.jb.framework.util.ColumnSorter;
import com.jb.framework.util.ListSorter;
import com.jb.framework.util.PropUtil;
import com.jb.util.ClassUtil;
import com.jb.util.Meter;
import com.jb.util.StringUtil;
import com.jb.web.application.Wizard;
import com.jb.web.bean.Form;
import com.jb.web.bean.ListBean;
import com.jb.web.bean.ListBox;
import com.jb.web.bean.TableCell;
import com.jb.web.bean.TableFooter;
import com.jb.web.bean.TableHeader;
import com.jb.web.bean.TableRow;
import com.jb.web.bean.event.UIEvent;
import com.jb.web.bean.inspector.PropertyInfo;
import com.jb.web.context.WebContext;
import com.jb.web.model.ChangeListener;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import java.util.ArrayList;
import java.util.Collections;
import java.util.Iterator;
import java.util.List;


/**
 * 
 */
public class DatabaseTable extends ListBean implements ChangeListener {
    static final long serialVersionUID = 123123L;

    public static class DTWizard extends Wizard
        {
            public DTWizard() {
                this.setType(DatabaseTable.class.getName());
                this.setPageName("dbListWizard");
                this.setPanelName("dataWizards");
            }

            public void create(WebContext ctx, UIEvent event) {
                super.create(ctx, event);

                Form form =(Form) event.getSource();
                ListBox select = (ListBox)form.getFormField("selectedColumns");
                if(select==null) {
                    ctx.addErrorMessage(this, "Can not find filed selectedColumns");
                    return;
                }
                List list = select.getSelected();
                Iterator iter = list.iterator();
                StringBuffer fields = new StringBuffer();
                while(iter.hasNext()) {
                    String columnName = (String) iter.next();
                    if(fields.length()>0)
                        fields.append(",");
                    fields.append(columnName);
                }
                
                DatabaseTable dt = (DatabaseTable)this.getObject();//created, we need to set
                //set client id
                String clientId = event.get("name");
                if(clientId!=null && clientId.length()>0)
                    dt.setClientId(clientId);

                Database db = ctx.getDatabase();
                Schema schema = db.getSchema();
                Table table = schema.getTable();
                dt.setSelectStatement("select "+fields.toString()+" from "+ schema.getName()+"."+ table.getName());

                dt.setCountStatement("select count(*) from "+ schema.getName()+"."+ table.getName());

                dt.setGenerateBinding(true);
                dt.generateBinding(ctx);
            }
            
            public void _create(WebContext ctx, UIEvent event) {
                super.create(ctx, event);

                Database db = ctx.getDatabase();
                Schema schema = db.getSchema();
                Table table = schema.getTable();

                List list = table.getColumns();
                Iterator iter = list.iterator();
                StringBuffer fields = new StringBuffer();
                while(iter.hasNext()) {
                    Column column = (Column) iter.next();
                    //
                    String param = event.get(column.getName());
                    if(param!=null) {//means this column is chosen
                        if(fields.length()>0)
                            fields.append(",");
                        fields.append(column.getName());
                    }
                }
                
                DatabaseTable dt = (DatabaseTable)this.getObject();//created, we need to set
                //set client id
                String clientId = event.get("name");
                if(clientId!=null && clientId.length()>0)
                    dt.setClientId(clientId);
                    
                dt.setSelectStatement("select "+fields.toString()+" from "+ schema.getName()+"."+ table.getName());
                dt.setCountStatement("select count(*) from "+ schema.getName()+"."+ table.getName());
                dt.setGenerateBinding(true);
                dt.generateBinding(ctx);
            }
        }
        
    static {
        registerWizard(DatabaseTable.class, DatabaseTable.DTWizard.class);

        publish(DatabaseTable.class, DATA, "selectStatement");
        publish(DatabaseTable.class, DATA, "countStatement");
        publish(DatabaseTable.class, DATA, "refreshInterval");
        publish(DatabaseTable.class, DATA, "groupBy");
        publish(DatabaseTable.class, DATA, "orderBy");
        publish(DatabaseTable.class, DATA, "fetchSize");
        publish(DatabaseTable.class, DATA, "whereClause");
    }


    private Predicate predicate;
    private ResultSetMetaData rmd;
    private String cachedStmt = "";
    private String countStatement = null;

    private String databaseUrl = "jdbc:mysql:///";

    private String groupBy;
    private String orderBy;
    private String whereClause;
    private String selectStatement = null;
    private boolean dirty = true;

    //
    //fetech from a table is expensive, and should be avoid when every necessary
    //if predicate did not change, mostly like the data set would not have changed
    //so going to database would be a lose. This will only be invalid if the 
    //database has been updated by other uses.
    //
    private int fetchSize = 200;
    private int totalCount;
    private long lastTimeRefreshed;
    private long refreshInterval;

    public DatabaseTable() {
        PersistentManagerRegistry.addChangeListener(this);
    }

    public void setClientId(String clientId) {
        if ((clientId != null) && !clientId.equals(getClientId())) {
            super.setClientId(clientId);
            //generate((WebContext) getFacesContext());
        }
    }

    public List getCol(int col) {
        ArrayList colList = new ArrayList();
        for (int i = 0; i < this.getSize(); i++) {
            colList.add(getValue(col, i));
        }
        return colList;
    }

    int col = -1;

    public void sort() {
        String sortBy = getSortBy();
        if ((sortBy == null) || (sortBy.length() == 0)) {
            return;
        }

        ListSorter sortor = null;

        try {
            col = Integer.parseInt(this.getSortBy());
        } catch (Exception e) {
            col = -1;
        }

        if (col == -1)
            return;
        sortor = new ColumnSorter(sortBy) {
                    public int compare(Object o1, Object o2) {
                        if ((o1 == null) || (o2 == null)) {
                            return 0;
                        }
                        List l1 = (List)o1;
                        List l2 = (List)o2;
                        Object v1 = l1.get(col);
                        Object v2 = l2.get(col);
                        return super.compare(v1, v2);
                    }
                };

        if (sortor != null) {
            sortor.setRevers(this.isReverse());
            Collections.sort(getList(), sortor);
            if (log.isDebugEnabled()) {
                log.debug("List is sorted");
            }
        }
    }

    public void setCountStatement(String countStatement) {
        this.countStatement = countStatement;
    }

    public String getCountStatement() {
        return countStatement;
    }

    public void setDatabaseUrl(String pDatabaseUrl) {
        this.databaseUrl = pDatabaseUrl;
    }

    public String getDatabaseUrl() {
        return databaseUrl;
    }

    public void setDirty(boolean dirty) {
        this.setStart(0);
        this.setSelected(0);
        this.dirty = dirty;
    }

    public void setFetchSize(int fetchSize) {
        this.fetchSize = fetchSize;
    }

    public int getFetchSize() {
        return fetchSize;
    }

    public void setGroupBy(String groupBy) {
        if(groupBy!=null&&!groupBy.equals(this.groupBy))
        {
            setDirty(true);
            this.groupBy = groupBy;
        }
    }

    public String getGroupBy() {
        return groupBy;
    }

    public List getList(WebContext ctx) {
        try {
            if(ctx.isRunTime())
            {   
                populate(ctx, null);
                this.sort();
            }
        } catch (SQLException e) {
            ctx.renderResponse();
        }
        return super.getList();
    }

    private List getMetaData(WebContext ctx) {
        if ((selectStatement == null) || (selectStatement.length() == 0)) {
            return Collections.EMPTY_LIST;
        }

        if (log.isDebugEnabled()) {
            log.debug("getMetaData");
        }

        String statement = null;

        try {
            statement = (String)AttributeEvaluator.evaluate(ctx, this, selectStatement);
        } catch (Exception e) {
            ctx.addErrorMessage(this, "Failed to parse statement" + selectStatement);
            if (log.isErrorEnabled())
                log.error(this, e);

            return Collections.EMPTY_LIST;
        }

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            List list = new ArrayList();
            conn = getConnection();
            stmt = conn.createStatement();
            
            if("MySQL".equals(conn.getMetaData().getDatabaseProductName()))
                statement = statement + " limit 1";
            
            rs = stmt.executeQuery(statement);

            int i = rs.getMetaData().getColumnCount();
            int j = 1;

            while (j < (i + 1)) {
                list.add(new PropertyInfo(rs.getMetaData().getColumnName(j), 
                                          rs.getMetaData().getColumnClassName(j)));
                j++;
            }
            conn.commit();
            return list;
        } catch (Exception e) {
            ctx.addErrorMessage(this, e.getMessage());
            if (log.isErrorEnabled())
                log.error(this, e);
            try {
                conn.rollback();
            } catch (SQLException _e) {
                log.error(this, _e);
            }
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (Exception e) {
                    if (log.isErrorEnabled())
                        log.error(this, e);
                }
            }

            ;

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (Exception e) {
                    if (log.isErrorEnabled())
                        log.error(this, e);
                }
            }
            ;

            DataSourceManager.releaseConnection(this, conn);
        }
        return Collections.EMPTY_LIST;
    }

    public void setOrderBy(String orderBy) {
        if(orderBy!=null&&!orderBy.equals(this.orderBy))
        {
            setDirty(true);
            this.orderBy = orderBy;
        }
    }

    public String getOrderBy() {
        return orderBy;
    }

    public void setPredicate(Predicate predicate) {
        if(predicate!=null&&!predicate.equals(this.predicate))
        {
            setDirty(true);
            this.predicate = predicate;
        }
    }

    public void setRefreshInterval(long refreshInterval) {
        this.refreshInterval = refreshInterval;
    }

    public long getRefreshInterval() {
        return refreshInterval;
    }

    public ResultSetMetaData getRowMetaData() {
        return rmd;
    }

    public void setSelectStatement(String selectStatement) {
        if ((selectStatement != null) && 
            !selectStatement.equals(this.selectStatement)) {
            this.selectStatement = selectStatement;
            setDirty(true);
        }
    }

    public String getSelectStatement() {
        return selectStatement;
    }

    public int getSize() {
        if (totalCount >= 0) {
            return this.totalCount;
        }
        return super.getSize();
    }

    public void setTotalCount(int totalCount) {
        this.totalCount = totalCount;
    }

    public int getTotalCount() {
        return totalCount;
    }

    public Object getValue(int col, int row) {
        return ((List)get(row)).get(col);
    }

    public void delete(String id) {
        //
        setDirty(true);
    }

    public void onCreate(PersistentContext ctx, Object object) {
        setDirty(true);
    }

    //when 

    public void onDelete(PersistentContext ctx, Object object) {
        setDirty(true);
    }

    public void onUpdate(PersistentContext ctx, Object object) {
        //
    }
    void populate(WebContext ctx, Predicate pPredicate) throws SQLException {
        if (log.isDebugEnabled()) {
            log.debug("populate " + selectStatement);
            Meter.start();
        }
        if (!dirty && (refreshInterval > 0) && (lastTimeRefreshed > 0) && 
            ((System.currentTimeMillis() - lastTimeRefreshed) < 
             (refreshInterval * 1000))) {
            return;
        }

        String statement = null;

        try {
            if ((selectStatement == null) || (selectStatement.length() == 0)) {
                return;
            }

            if (log.isDebugEnabled()) {
                log.debug("populate data from database");
            }

            if (!dirty && "session".equals(getScope())) {
                List list = (List)ctx.getSession().getAttribute(getId());

                if (list != null) {
                    setList(list);

                    return;
                }
            }
/* TODO
            if ("application".equals(getScope())) {
                List list = (List)ctx.getApplication().getAttribute(getId());

                if (list != null) {
                    setList(list);

                    return;
                }
            }
*/
            statement = selectStatement;
            if (statement == null) {
                ctx.addErrorMessage(this, "Sql statement is null " + statement);
                return;
            }

            statement.trim();

            if (statement.endsWith(";")) {
                statement = statement.substring(0, statement.length() - 1);
            }

            Predicate pred = null;
            if (whereClause != null && whereClause.trim().length()>0)
                pred = new Predicate(whereClause);
            
            if(pred!=null)
                pred = pred.AND(this.predicate).AND(pPredicate);
            else
                pred = this.predicate;
             
            if(pred!=null)
                pred = pred.AND(pPredicate);
            else
                pred = pPredicate;

            statement = statement + ((pred == null) ? "" : (" where " + pred.toString())) + 
                    (((groupBy == null) || (groupBy.length() == 0)) ? "" : 
                     (" group by " + groupBy)) + 
                    (((orderBy == null) || (orderBy.length() == 0)) ? "" : 
                     (" order by " + orderBy));

            Connection conn = null;
            SelectDBCommand sdbc =null;
            List list = new ArrayList();

            try {
                conn = getConnection();
                if (fetchSize > 0 && conn.getMetaData().getDatabaseProductName().equals("MySQL")) {
                    statement = statement + " limit " + fetchSize;
                }

                try {
                    statement = (String)AttributeEvaluator.evaluate(ctx, this, statement);
                } catch (Exception e) {
                    if (log.isErrorEnabled())
                        log.error(this, e);

                    ctx.addErrorMessage(this, "Failed to parse statement" + selectStatement);

                    return;
                }

                if (!statement.equals(cachedStmt)) {
                    setDirty(true);
                    cachedStmt = statement;
                }

                sdbc = new SelectDBCommand(cachedStmt,  pred) {
                        public Object populate(Object list) throws SQLException {
                            ResultSet rs = getResultSet();
                            String beanClass = getBeanClass();

                            if ((beanClass != null) && 
                                (beanClass.length() != 0)) {
                                Object mb = ClassUtil.createInstance(beanClass);
                                if(mb!=null)
                                {
                                    PropUtil.updateWithResultSet(mb, rs);
                                        ((List)list).add(mb);
                                }
                                return mb;
                            } else {
                                List alist = new ArrayList();

                                for (int j = 1; j < (rmd.getColumnCount() + 1); 
                                     j++)
                                    alist.add(rs.getObject(j));

                                ((List)list).add(alist);

                                return alist;
                            }
                        }
                    };

                getCount(ctx, conn);
                sdbc.execute(conn);

                rmd = sdbc.getResultSet().getMetaData();

                int count = 0;

                while (sdbc.next()) {
                    sdbc.populate(list);
                    if (fetchSize > 0)
                        if (count >= fetchSize)
                            break;
                    count++;
                }
                conn.commit();
            }catch(SQLException e) {
                if (log.isErrorEnabled())
                    log.error(this, e);
                conn.rollback();
                ctx.addErrorMessage(this, e.getMessage());
                throw e;
            } finally {
                if(sdbc!=null)
                    sdbc.destroy();
                DataSourceManager.releaseConnection(this, conn);                
            }

            setList(list);
            dirty = false;
            lastTimeRefreshed = System.currentTimeMillis();

            if ("session".equals(getScope())) {
                ctx.getSession().addAttribute(getId(), list);
            }
/* TODO
            if ("application".equals(getScope())) {
                ctx.getApplication().setAttribute(getId(), list);
            }*/
        } finally {
        }
    }
    {
        if (log.isDebugEnabled()) {
            Meter.stop("Populate from database " + this.selectStatement);
        }
    }

    private Connection getConnection() throws SQLException {
        return DataSourceManager.getConnection(this);
    }

    private void getCount(WebContext ctx, Connection conn) {
        if(!dirty) {
            return ;
        }
        totalCount = -1;
        String statement = getCountStatement();

        if ((statement == null) || (statement.length() == 0)) {
            return;
        }

        try {
            statement = (String)AttributeEvaluator.evaluate(ctx, this, statement);
        } catch (Exception e) {
            if (log.isErrorEnabled())
                log.error(this, e);

            ctx.addErrorMessage(this, "Failed to parse statement" + selectStatement);

            return;
        }

        try {
            statement.trim();

            SelectDBCommand sdbc = new SelectDBCommand(statement, null) {
                    public Object populate(Object visitor) throws SQLException {
                        return null;
                    }
                };

            try {
                sdbc.execute(conn);

                while (sdbc.next()) {
                    totalCount = sdbc.getResultSet().getInt(1);
                }
            } finally {
                sdbc.destroy();
            }
        } catch (SQLException e) {
            if (log.isErrorEnabled())
                log.error(this, e);
            ctx.addErrorMessage(this, e.getMessage());
        }
    }

    /**
     * Generates form from table structure.
     * @param ctx
     */
    protected void generateBinding(WebContext ctx) {
        if (ctx == null) {
            return;
        }
        _generateBinding = false;

        List columns = getMetaData(ctx);

        if (columns.size() == 0) {
            ctx.addErrorMessage(this, "Columns not found " + this.selectStatement);
            return;
        }
        //setShowFooter(true);
        //   setShowRowNo(false);
        setRows(1);

        TableFooter footer = getFooter(ctx);

        footer.setCols(1);

        TableCell fCell = footer.getCellAt(0);
        fCell.setColSpan(columns.size());
        fCell.clearChildren();

        TableHeader titleRow = getHeader(ctx);
        titleRow.setCols(columns.size());

        TableRow dataRow = getRow(0);
        dataRow.setCols(columns.size());

        valueChanged(ctx);

        int i = 0;
        Iterator iter = columns.iterator();

        while (iter.hasNext()) {
            PropertyInfo pi = (PropertyInfo)iter.next();
            TableCell tCell = titleRow.getCellAt(i);
            TableCell dCell = dataRow.getCellAt(i);
            if (tCell != null) {
                tCell.setValue(StringUtil.itemNameToLabel(pi.getPropertyName()));
            }
            if (dCell != null) {
                dCell.setValue("${" + getClientId() + ".row[" + i + "]}");
            }
            i++;
        }
    }

    public void setWhereClause(String param) {
        this.whereClause = param;
    }

    public String getWhereClause() {
        return whereClause;
    }
}
